using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Class contains generic data access functionality to be accessed from 
/// the business tier
/// </summary>
public static class GenericDataAccess
{
    // static constructor 
    // Obtain the database connection string
    private static String connectionString = "Data Source=PHUONG\\SQLEXPRESS; Initial Catalog= QUANLIBOMON;Integrated Security=True";
   
    static GenericDataAccess()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    // execute a command and return the results as a DataTable object
    public static DataTable ExecuteSelectCommand(DbCommand command)
    {
        // The DataTable to be returned 
        DataTable table;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the data connection 
            command.Connection.Open();
            // Execute the command and save the results in a DataTable
            DbDataReader reader = command.ExecuteReader();

            

            table = new DataTable();
            

            table.Load(reader);
           
            // Close the reader 
            reader.Close();
        }
        catch (Exception ex)
        {
            
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        return table;
    }

    // execute an update, delete, or insert command 
    // and return the number of affected rows
    public static int ExecuteNonQuery(DbCommand command)
    {
        // The number of affected rows 
        int affectedRows = -1;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            affectedRows = command.ExecuteNonQuery();
            
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
            
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the number of affected rows
        return affectedRows;
    }

    // execute a select command and return a single result as a string
    public static string ExecuteScalar(DbCommand command)
    {
        // The value to be returned 
        string value = "";
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            value = command.ExecuteScalar().ToString();
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
            
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the result
        return value;
    }

    // creates and prepares a new DbCommand object on a new connection
    public static DbCommand CreateCommand()
    {
        
        
        // Create a new data provider factory
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        // Obtain a database specific connection object
        DbConnection conn = factory.CreateConnection();
        // Set the connection string
        conn.ConnectionString = connectionString;
        // Create a database specific command object
        DbCommand comm = conn.CreateCommand();
        // Set the command type to stored procedure
        comm.CommandType = CommandType.StoredProcedure;
        // Return the initialized command object
        return comm;
    }

    // execute a command and return the results as a DataSet object
    public static DataSet DataSet_ExecuteSelectCommand(SqlCommand sqlcommand,int startRecord, int maxRecords, string srcTable,out int SoDongDaFill)
    {
         DataSet Ds = new DataSet();
        try
        {


            SqlConnection conn = new SqlConnection();

            // Set the connection string
            conn.ConnectionString = connectionString;
            // Create a database specific command object
            SqlCommand comm = conn.CreateCommand();
            // Set the command type to stored procedure
            comm.CommandType = CommandType.StoredProcedure;

        
            sqlcommand.Connection.Open();


            SqlDataAdapter Da = new SqlDataAdapter(sqlcommand);

                       

         SoDongDaFill=   Da.Fill(Ds, startRecord, maxRecords, srcTable);

           


           
            
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            // Close the connection
            sqlcommand.Connection.Close();
        }
        return Ds;
    }
    // execute a command and return the results as a DataSet object
    public static DataSet DataSet_ExecuteSelectCommand(String CommandText)
    {
        DataSet Ds = new DataSet();
        SqlCommand comm = new SqlCommand() ;
        try
        {


            SqlConnection conn = new SqlConnection();

            // Set the connection string
            conn.ConnectionString = connectionString;
            // Create a database specific command object
            comm = conn.CreateCommand();
            // Set the command type to stored procedure
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = CommandText;


            comm.Connection.Open();


            SqlDataAdapter Da = new SqlDataAdapter(comm);
            Da.Fill(Ds);

        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            // Close the connection
            comm.Connection.Close();
        }
        return Ds;
    }





}
